﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Data.SqlClient;

namespace EntityCreate
{
    [Serializable]
    public class Column : ISerializable
    {
        public string Name { get; set; }
        public string DataType { get; set; }
        public bool Nullable { get; set; }
        public bool Identity { get; set; }
        public string Collation { get; set; }
        public string EntityName { get; set; }

        public Column() { }
        public Column(SerializationInfo info, StreamingContext ctxt)
        {
            Name = (string)info.GetValue("Name", typeof(string));
            DataType = (string)info.GetValue("DataType", typeof(string));
            Nullable = (bool)info.GetValue("Nullable", typeof(bool));
            Identity = (bool)info.GetValue("Identity", typeof(bool));
            Collation = (string)info.GetValue("Collation", typeof(string));
            EntityName = (string)info.GetValue("EntityName", typeof(string));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Name", Name);
            info.AddValue("DataType", DataType);
            info.AddValue("Nullable", Nullable);
            info.AddValue("Identity", Identity);
            info.AddValue("Collation", Collation);
            info.AddValue("EntityName", EntityName);
        }

        public bool Compare(Column model)
        {
            if (this.DataType != model.DataType || this.Nullable != model.Nullable || this.Identity != model.Identity)
                return false;
            else
                return true;
        }
    }

    [Serializable]
    public class Table : ISerializable
    {
        List<Column> _columns = new List<Column>();
        List<Index> _indexes = new List<Index>();

        public List<Column> Columns { get { return _columns; } set { _columns = value; } }
        public List<Index> Indexes { get { return _indexes; } set { _indexes = value; } }

        public string Name { get; set; }
        public string EntityName { get; set; }
        public string EntityCogulAdi { get; set; }


        public Table() { }
        public Table(SerializationInfo info, StreamingContext ctxt)
        {
            Columns = (List<Column>)info.GetValue("Columns", typeof(List<Column>));
            Indexes = (List<Index>)info.GetValue("Indexes", typeof(List<Index>));
            Name = (string)info.GetValue("Name", typeof(string));
            EntityName = (string)info.GetValue("EntityName", typeof(string));
            EntityCogulAdi = (string)info.GetValue("EntityCogulAdi", typeof(string));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Columns", Columns);
            info.AddValue("Indexes", Indexes);
            info.AddValue("Name", Name);
            info.AddValue("EntityName", EntityName);
            info.AddValue("EntityCogulAdi", EntityCogulAdi);
        }

        public Column FindColumn(string name)
        {
            foreach (Column col in this.Columns)
            {
                if (col.Name == name)
                    return col;
            }
            return null;
        }

        public IEnumerable<Column> ToInsertingColumn(Table model)
        {
            foreach (Column col in model.Columns)
            {
                Column c = FindColumn(col.Name);
                if (c == null)
                {
                    yield return col;
                }
            }
        }
        public IEnumerable<Column> ToChangingColumn(Table model)
        {
            foreach (Column col in model.Columns)
            {
                Column c = FindColumn(col.Name);
                if (c != null)
                {
                    if (!c.Compare(col)) // farklı
                        yield return col;
                }
            }
        }
        public IEnumerable<Column> ToDeletingColumn(Table model)
        {
            foreach (Column col in this.Columns)
            {
                Column c = model.FindColumn(col.Name);
                if (c == null)
                {
                    yield return col;
                }
            }
        }
        public IEnumerable<Column> ToCollationColumn(Table model)
        {
            foreach (Column col in this.Columns)
            {
                Column c = model.FindColumn(col.Name);
                if (c != null && c.Collation != col.Collation)
                {
                    yield return col;
                }
            }
        }
    }

    [Serializable]
    public class Index : ISerializable
    {
        public string Name { get; set; }
        public string ColumnNames { get; set; }
        public bool Clustered { get; set; }
        public bool Unique { get; set; }
        public bool PrimaryKey { get; set; }

        public Index() { }
        public Index(SerializationInfo info, StreamingContext ctxt)
        {
            Name = (string)info.GetValue("Name", typeof(string));
            ColumnNames = (string)info.GetValue("ColumnNames", typeof(string));
            Clustered = (bool)info.GetValue("Clustered", typeof(bool));
            Unique = (bool)info.GetValue("Unique", typeof(bool));
            PrimaryKey = (bool)info.GetValue("PrimaryKey", typeof(bool));
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Name", Name);
            info.AddValue("ColumnNames", ColumnNames);
            info.AddValue("Clustered", Clustered);
            info.AddValue("Unique", Unique);
            info.AddValue("PrimaryKey", PrimaryKey);
        }
        public bool Compare(Index model)
        {
            if (this.ColumnNames != model.ColumnNames || this.Clustered != model.Clustered || this.Unique != model.Unique)
                return false;
            else
                return true;
        }
    }

    public class Database
    {
        public List<Table> GetSchema(string cnnString)
        {
            List<Table> Tables = new List<Table>();
            using (SqlConnection cn = new SqlConnection(cnnString))
            {
                cn.Open();
                using (SqlCommand cmd = new SqlCommand("select name from sysobjects where xtype='U' order by name", cn))
                {
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        Tables.Add(new Table() { Name = rd.GetString(0) });
                    }
                    rd.Close();


                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("SELECT c.name,case when c.precision =0 then (select name from sys.types where user_type_id=c.user_type_id) + '(' + cast(c.max_length as varchar) + ')' else (select name from sys.types where user_type_id=c.user_type_id) end as datatype,c.is_nullable,c.is_identity,c.collation_name ");
                    sb.AppendLine(" FROM sys.columns as c WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects ");
                    sb.AppendLine(" WHERE type = 'U' AND name='{0}') order by column_id ");

                    foreach (Table tbl in Tables)
                    {
                        cmd.CommandText = string.Format(sb.ToString(), tbl.Name);
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            Column cl = new Column();
                            cl.Collation = rd["collation_name"].ToString();

                            if (rd["datatype"].ToString().IndexOf("image") > -1)
                            {
                                cl.DataType = rd["datatype"].ToString().Substring(0, rd["datatype"].ToString().IndexOf('('));
                            }
                            else
                                cl.DataType = rd["datatype"].ToString().Replace("-1", "MAX");

                            cl.Identity = Convert.ToBoolean(rd["is_identity"]);
                            cl.Name = rd["name"].ToString();
                            cl.Nullable = Convert.ToBoolean(rd["is_nullable"]);
                            tbl.Columns.Add(cl);
                        }
                        rd.Close();
                    }

                    foreach (Table tbl in Tables)
                    {
                        cmd.CommandText = string.Format("EXEC sp_helpindex '{0}'", tbl.Name);
                        rd = cmd.ExecuteReader();
                        while (rd.Read())
                        {
                            Index idx = new Index();
                            idx.Clustered = false;
                            idx.Unique = false;
                            idx.PrimaryKey = false;
                            idx.ColumnNames = rd["index_keys"].ToString();
                            idx.Name = rd["index_name"].ToString();

                            string desc = rd["index_description"].ToString();

                            if (desc.ToLower().IndexOf("nonclustered") > -1)
                                idx.Clustered = false;
                            else if (desc.ToLower().IndexOf("clustered") > -1)
                                idx.Clustered = true;

                            if (desc.ToLower().IndexOf("primary key") > -1)
                                idx.PrimaryKey = true;

                            if (desc.ToLower().IndexOf("unique") > -1)
                                idx.Unique = true;

                            tbl.Indexes.Add(idx);
                        }
                        rd.Close();
                    }

                }
                cn.Close();
            }
            return Tables;
        }
    }
}
